package sfeir.ergosum.dao;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.StreamCorruptedException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import sfeir.ergosum.model.Contact;
import sfeir.ergosum.model.Group;
import sfeir.ergosum.model.LetterIndex;
import sfeir.ergosum.model.Person;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteCursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseProvider {

    protected static final String DATABASE_NAME = "ergosum.db";
    protected static final String DATABASE_TABLE_GROUP = "groupt";
    protected static final String DATABASE_TABLE_CONTACT = "contact";
    public static final String DATABASE_TABLE_LETTERINDEX = "letterindex";
    public static final String DATABASE_TABLE_PHONEINDEX = "phoneindex";
    public static final String DATABASE_TABLE_MAILINDEX = "mailindex";
    public static final String DATABASE_TABLE_FNLETTERINDEX = "fnletterindex";
    public static final String DATABASE_TABLE_FNPHONEINDEX = "fnphoneindex";
    public static final String DATABASE_TABLE_FNMAILINDEX = "fnmailindex";
    protected static final int DATABASE_VERSION = 36;

    protected static final String alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    // The index (key) column name for use in where clauses.
    public static final String KEY_ID = "_id";
    public static final int KEY_ID_COLUMN = 0;
    // The name and column index of each column in your database.

    public static final String KEY_GROUP_ORDER = "position";
    public static final int GROUP_ORDER_COLUMN = 1;

    public static final String KEY_GROUP_LABEL = "label";
    public static final int GROUP_LABEL_COLUMN = 2;

    public static final String KEY_CONTACT_GROUP = "group_id";
    public static final int CONTACT_GROUP_COLUMN = 1;

    public static final String KEY_CONTACT_PERSON = "person";
    public static final int CONTACT_PERSON_COLUMN = 2;

    public static final String KEY_CONTACT_URL = "url";
    public static final int CONTACT_URL_COLUMN = 3;

    public static final String KEY_CONTACT_ALIAS = "alias";
    public static final int CONTACT_ALIAS_COLUMN = 4;

    public static final String KEY_CONTACT_UPDATE = "updated";
    public static final int CONTACT_UPDATE_COLUMN = 5;

    public static final String KEY_CONTACT_PHONEID = "phoneid";
    public static final int CONTACT_PHONEID_COLUMN = 6;

    public static final String KEY_CONTACT_LETTERID = "letterid";
    public static final int CONTACT_LETTERID_COLUMN = 7;

    public static final String KEY_CONTACT_PHONEINDEX = "phoneindex";
    public static final int CONTACT_PHONEINDEX_COLUMN = 8;

    public static final String KEY_CONTACT_MAILINDEX = "mailindex";
    public static final int CONTACT_MAILINDEX_COLUMN = 9;

    public static final String KEY_CONTACT_FNLETTERID = "fnletter";
    public static final int CONTACT_FNLETTERID_COLUMN = 10;

    public static final String KEY_CONTACT_FNPHONEINDEX = "fnphone";
    public static final int CONTACT_FNPHONEINDEX_COLUMN = 11;

    public static final String KEY_CONTACT_FNMAILINDEX = "fnmail";
    public static final int CONTACT_FNMAILINDEX_COLUMN = 12;

    public static final String KEY_CONTACT_ACCESS = "access";
    public static final int CONTACT_ACCESS_COLUMN = 13;

    public static final String KEY_INDEX_LETTER = "letter";
    public static final int INDEX_LETTER_COLUMN = 1;

    public static final String KEY_INDEX_OCCU = "occu";
    public static final int INDEX_OCCU_COLUMN = 2;

    // SQL Statement to create a new database.

    protected static final String DATABASE_CREATE_GROUP = "create table "
            + DATABASE_TABLE_GROUP + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_GROUP_LABEL
            + " text not null, " + KEY_GROUP_ORDER + " text not null " + ");";

    protected static final String DATABASE_CREATE_CONTACT = "create table "
            + DATABASE_TABLE_CONTACT + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_CONTACT_GROUP
            + " text not null, " + KEY_CONTACT_PERSON + " blob not null, "
            + KEY_CONTACT_URL + " text not null, " + KEY_CONTACT_ALIAS
            + " text, " + KEY_CONTACT_UPDATE + " integer, "
            + KEY_CONTACT_PHONEID + " integer, " + KEY_CONTACT_LETTERID
            + " integer, " + KEY_CONTACT_PHONEINDEX + " integer, "
            + KEY_CONTACT_MAILINDEX + " integer, " + KEY_CONTACT_FNLETTERID
            + " integer, " + KEY_CONTACT_FNPHONEINDEX + " integer, "
            + KEY_CONTACT_FNMAILINDEX + " integer, " + KEY_CONTACT_ACCESS
            + " integer " + ");";

    protected static final String DATABASE_CREATE_CONTACT_INDEX = "CREATE INDEX "
            + DATABASE_TABLE_CONTACT
            + "index ON "
            + DATABASE_TABLE_CONTACT
            + "(" + KEY_CONTACT_GROUP + ");";

    protected static final String DATABASE_CREATE_CONTACT_INDEX2 = "CREATE INDEX "
            + DATABASE_TABLE_CONTACT
            + "index2 ON "
            + DATABASE_TABLE_CONTACT
            + "(" + KEY_ID + ");";

    protected static final String DATABASE_CREATE_LETTERINDEX = "create table "
            + DATABASE_TABLE_LETTERINDEX + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_INDEX_LETTER
            + " text not null, " + KEY_INDEX_OCCU + " integer );";

    protected static final String DATABASE_CREATE_PHONEINDEX = "create table "
            + DATABASE_TABLE_PHONEINDEX + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_INDEX_LETTER
            + " text not null, " + KEY_INDEX_OCCU + " integer );";

    protected static final String DATABASE_CREATE_MAILINDEX = "create table "
            + DATABASE_TABLE_MAILINDEX + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_INDEX_LETTER
            + " text not null, " + KEY_INDEX_OCCU + " integer );";

    protected static final String DATABASE_CREATE_FNLETTERINDEX = "create table "
            + DATABASE_TABLE_FNLETTERINDEX
            + " ("
            + KEY_ID
            + " integer primary key autoincrement, "
            + KEY_INDEX_LETTER
            + " text not null, " + KEY_INDEX_OCCU + " integer );";

    protected static final String DATABASE_CREATE_FNPHONEINDEX = "create table "
            + DATABASE_TABLE_FNPHONEINDEX
            + " ("
            + KEY_ID
            + " integer primary key autoincrement, "
            + KEY_INDEX_LETTER
            + " text not null, " + KEY_INDEX_OCCU + " integer );";

    protected static final String DATABASE_CREATE_FNMAILINDEX = "create table "
            + DATABASE_TABLE_FNMAILINDEX + " (" + KEY_ID
            + " integer primary key autoincrement, " + KEY_INDEX_LETTER
            + " text not null, " + KEY_INDEX_OCCU + " integer );";

    // Variable to hold the database instance
    private SQLiteDatabase db;

    // Database open/upgrade helper
    private SQLiteOpenHelper dbConnection;

    public DatabaseProvider(SQLiteOpenHelper dbConnection) {
        this.dbConnection = dbConnection;
    }

    public DatabaseProvider open() throws SQLException {
        try {
            db = dbConnection.getWritableDatabase();
        } catch (SQLiteException ex) {
            db = dbConnection.getReadableDatabase();
        }
        return this;
    }

    public void close() {
        db.close();
    }

    /**
     * Inserts a new group inside the database
     * 
     * @param group
     *            The group to be inserted
     * @return The id of group in database
     */
    public long insertGroup(Group group) {
        ContentValues groupValues = new ContentValues();
        groupValues.put(KEY_GROUP_ORDER, group.getOrder());
        groupValues.put(KEY_GROUP_LABEL, group.getName());
        return db.insert(DATABASE_TABLE_GROUP, null, groupValues);
    }

    /**
     * Updates a group in database
     * 
     * @param group
     *            The group to update
     * @return The group id
     */
    public long updateGroup(Group group) {

        String whereGroup = KEY_ID + "=" + group.getId();

        ContentValues groupValues = new ContentValues();
        groupValues.put(KEY_GROUP_ORDER, group.getOrder());
        groupValues.put(KEY_GROUP_LABEL, group.getName());
        db.update(DATABASE_TABLE_GROUP, groupValues, whereGroup, null);
        return group.getId();
    }

    /**
     * Returns all the groups in database + a default group called "Contacts"
     * with id -1
     * 
     * @return A list of groups
     */
    public List<Group> getAllGroups() {
        List<Group> groups = new ArrayList<Group>();
        Cursor allGroups = db.query(DATABASE_TABLE_GROUP, new String[] {
                KEY_ID, KEY_GROUP_ORDER, KEY_GROUP_LABEL }, null, null, null,
                null, null);

        if (allGroups.moveToFirst()) {
            do {
                Group g = new Group(allGroups.getInt(KEY_ID_COLUMN), allGroups
                        .getInt(GROUP_ORDER_COLUMN), allGroups
                        .getString(GROUP_LABEL_COLUMN));
                groups.add(g);
            } while (allGroups.moveToNext());
        }

        allGroups.close();

        Collections.sort(groups, new Comparator<Group>() {
            @Override
            public int compare(Group object1, Group object2) {
                return Float.compare(object1.getOrder(), object2.getOrder());
            }
        });

        Group defaultgroup = new Group(-1, -1, "Contacts");
        groups.add(defaultgroup);
        return groups;
    }

    /**
     * Deletes a group from database
     * 
     * @param groupId
     *            The id of the group to delete
     * @return true if something has been deleted, else false
     */
    public boolean removeGroup(long groupId) {

        String whereContacts = KEY_CONTACT_GROUP + "=" + groupId;
        Cursor validContacts = db.query(DATABASE_TABLE_CONTACT,
                new String[] { KEY_ID }, whereContacts, null, null, null, null);

        if (validContacts.moveToFirst()) {

            do {
                String whereContact = KEY_ID + "="
                        + validContacts.getInt(KEY_ID_COLUMN);
                ContentValues cv = new ContentValues();
                cv.put(KEY_CONTACT_GROUP, -1);
                db.update(DATABASE_TABLE_CONTACT, cv, whereContact, null);
            } while (validContacts.moveToNext());

        }

        validContacts.close();

        return db.delete(DATABASE_TABLE_GROUP, KEY_ID + "=" + groupId, null) > 0;
    }

    /**
     * Gets all the contacts in a specific group
     * 
     * @param groupId
     *            The group to get all the contacts for
     * @return An ArrayList of contacts
     */
    public List<Contact> getAllContactsFromGroup(long groupId) {
        List<Contact> groupContacts = new ArrayList<Contact>();

        String whereContacts = KEY_CONTACT_GROUP + "=" + groupId;
        Cursor validContacts = db.query(DATABASE_TABLE_CONTACT, null,
                whereContacts, null, null, null, null);

        if (validContacts.moveToFirst()) {

            do {
                Contact contact = new Contact();
                contact.setId(validContacts.getLong(KEY_ID_COLUMN));
                contact.setGroupId(validContacts.getInt(CONTACT_GROUP_COLUMN));

                ByteArrayInputStream bais = new ByteArrayInputStream(
                        validContacts.getBlob(CONTACT_PERSON_COLUMN));
                ObjectInputStream ois;
                try {
                    ois = new ObjectInputStream(bais);
                    contact.setPerson((Person) ois.readObject());
                    ois.close();
                    bais.close();
                } catch (StreamCorruptedException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }

                contact.setUrl(validContacts.getString(CONTACT_URL_COLUMN));
                contact.setAlias(validContacts.getString(CONTACT_ALIAS_COLUMN));
                contact
                        .setUpdated(validContacts
                                .getLong(CONTACT_UPDATE_COLUMN));
                contact.setPhoneId(validContacts
                        .getLong(CONTACT_PHONEID_COLUMN));
                contact.setLetterId(validContacts
                        .getLong(CONTACT_LETTERID_COLUMN));
                contact.setPhoneIndex(validContacts
                        .getLong(CONTACT_PHONEINDEX_COLUMN));
                contact.setMailIndex(validContacts
                        .getLong(CONTACT_MAILINDEX_COLUMN));
                contact.setFnletter(validContacts
                        .getLong(CONTACT_FNLETTERID_COLUMN));
                contact.setFnphone(validContacts
                        .getLong(CONTACT_FNPHONEINDEX_COLUMN));
                contact.setFnmail(validContacts
                        .getLong(CONTACT_FNMAILINDEX_COLUMN));
                contact.setAccess(validContacts.getLong(CONTACT_ACCESS_COLUMN));

                groupContacts.add(contact);
            } while (validContacts.moveToNext());

        }

        validContacts.close();

        return sortContacts(groupContacts);
    }

    /**
     * Inserts a contact and its person in the database
     * 
     * @param contact
     *            Contact to be inserted in database
     * @return The id of the inserted contact
     */
    public long insertContact(Contact contact) {

        Person p = contact.getPerson();

        ByteArrayOutputStream baos = new ByteArrayOutputStream();

        try {
            ObjectOutputStream oos = new ObjectOutputStream(baos);
            oos.writeObject(p);
        } catch (IOException e) {
            e.printStackTrace();
        }

        ContentValues contactValues = new ContentValues();
        contactValues.put(KEY_CONTACT_GROUP, -1);
        contactValues.put(KEY_CONTACT_PERSON, baos.toByteArray());
        contactValues.put(KEY_CONTACT_URL, contact.getUrl());
        contactValues.put(KEY_CONTACT_ALIAS, contact.getAlias());
        contactValues.put(KEY_CONTACT_UPDATE, contact.getUpdated());
        contactValues.put(KEY_CONTACT_PHONEID, -1);
        contactValues.put(KEY_CONTACT_ACCESS, contact.getAccess());

        String name = contact.getPerson().getName();
        String firstName = contact.getPerson().getFirstname();
        long index;

        index = getIndex(name, DATABASE_TABLE_LETTERINDEX);
        incrementIndex(index, DATABASE_TABLE_LETTERINDEX);
        contactValues.put(KEY_CONTACT_LETTERID, index);

        if (p.getTels().size() > 0) {
            index = getIndex(name, DATABASE_TABLE_PHONEINDEX);
            incrementIndex(index, DATABASE_TABLE_PHONEINDEX);
            contactValues.put(KEY_CONTACT_PHONEINDEX, index);
        }

        if (p.getEmails().size() > 0) {
            index = getIndex(name, DATABASE_TABLE_MAILINDEX);
            incrementIndex(index, DATABASE_TABLE_MAILINDEX);
            contactValues.put(KEY_CONTACT_MAILINDEX, index);
        }

        if (firstName != null && firstName.length() > 0 && name.length() > 0
                && !(firstName.substring(0, 1).equals(name.substring(0, 1)))) {
            index = getIndex(firstName, DATABASE_TABLE_FNLETTERINDEX);
            incrementIndex(index, DATABASE_TABLE_FNLETTERINDEX);
            contactValues.put(KEY_CONTACT_FNLETTERID, index);

            if (p.getTels().size() > 0) {
                index = getIndex(firstName, DATABASE_TABLE_FNPHONEINDEX);
                incrementIndex(index, DATABASE_TABLE_FNPHONEINDEX);
                contactValues.put(KEY_CONTACT_FNPHONEINDEX, index);
            }

            if (p.getEmails().size() > 0) {
                index = getIndex(firstName, DATABASE_TABLE_FNMAILINDEX);
                incrementIndex(index, DATABASE_TABLE_FNMAILINDEX);
                contactValues.put(KEY_CONTACT_FNMAILINDEX, index);
            }
        }

        return db.insert(DATABASE_TABLE_CONTACT, null, contactValues);
    }

    /**
     * Deletes a contact from database, with its person
     * 
     * @param contact
     *            Contact to be deleted
     * @return true if contact has been successfully deleted
     */
    public boolean removeContact(Contact contact) {

        if (contact.getLetterId() != -1)
            decrementIndex(contact.getLetterId(), DATABASE_TABLE_LETTERINDEX);
        if (contact.getPhoneIndex() != -1)
            decrementIndex(contact.getPhoneIndex(), DATABASE_TABLE_PHONEINDEX);
        if (contact.getMailIndex() != -1)
            decrementIndex(contact.getMailIndex(), DATABASE_TABLE_MAILINDEX);
        if (contact.getLetterId() != -1)
            decrementIndex(contact.getFnletter(), DATABASE_TABLE_FNLETTERINDEX);
        if (contact.getPhoneIndex() != -1)
            decrementIndex(contact.getFnphone(), DATABASE_TABLE_FNPHONEINDEX);
        if (contact.getMailIndex() != -1)
            decrementIndex(contact.getFnmail(), DATABASE_TABLE_FNMAILINDEX);

        return (db.delete(DATABASE_TABLE_CONTACT, KEY_ID + "="
                + contact.getId(), null) > 0);
    }

    /**
     * Deletes a contact from database
     * 
     * @param contactId
     *            The id of the group to delete
     * @return true if something has been deleted, else false
     */
    public boolean removeContact(long contactId) {

        Contact contact = getContact(contactId);

        if (contact.getLetterId() != -1)
            decrementIndex(contact.getLetterId(), DATABASE_TABLE_LETTERINDEX);
        if (contact.getPhoneIndex() != -1)
            decrementIndex(contact.getPhoneIndex(), DATABASE_TABLE_PHONEINDEX);
        if (contact.getMailIndex() != -1)
            decrementIndex(contact.getMailIndex(), DATABASE_TABLE_MAILINDEX);
        if (contact.getLetterId() != -1)
            decrementIndex(contact.getFnletter(), DATABASE_TABLE_FNLETTERINDEX);
        if (contact.getPhoneIndex() != -1)
            decrementIndex(contact.getFnphone(), DATABASE_TABLE_FNPHONEINDEX);
        if (contact.getMailIndex() != -1)
            decrementIndex(contact.getFnmail(), DATABASE_TABLE_FNMAILINDEX);

        return (db.delete(DATABASE_TABLE_CONTACT, KEY_ID + "=" + contactId,
                null) > 0);
    }

    /**
     * Gets a full contact (including person) from the database
     * 
     * @param contactId
     *            The wanted contact id
     * @return The contact
     */
    public Contact getContact(long contactId) {

        Contact c = new Contact();
        c.setId(contactId);
        String whereContact = KEY_ID + "=" + contactId;
        Cursor contactResult = db.query(DATABASE_TABLE_CONTACT, null,
                whereContact, null, null, null, null);

        if (contactResult.getCount() > 0) {

            if (contactResult.moveToFirst()) {

                c.setId(contactId);
                c.setGroupId(contactResult.getInt(CONTACT_GROUP_COLUMN));

                ByteArrayInputStream bais = new ByteArrayInputStream(
                        contactResult.getBlob(CONTACT_PERSON_COLUMN));
                ObjectInputStream ois;
                try {
                    ois = new ObjectInputStream(bais);
                    c.setPerson((Person) ois.readObject());
                    ois.close();
                    bais.close();
                } catch (StreamCorruptedException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }

                c.setUrl(contactResult.getString(CONTACT_URL_COLUMN));
                c.setAlias(contactResult.getString(CONTACT_ALIAS_COLUMN));
                c.setUpdated(contactResult.getLong(CONTACT_UPDATE_COLUMN));
                c.setPhoneId(contactResult.getLong(CONTACT_PHONEID_COLUMN));
                c.setLetterId(contactResult.getLong(CONTACT_LETTERID_COLUMN));
                c.setPhoneIndex(contactResult
                        .getLong(CONTACT_PHONEINDEX_COLUMN));
                c.setMailIndex(contactResult.getLong(CONTACT_MAILINDEX_COLUMN));
                c.setFnletter(contactResult.getLong(CONTACT_FNLETTERID_COLUMN));
                c
                        .setFnphone(contactResult
                                .getLong(CONTACT_FNPHONEINDEX_COLUMN));
                c.setFnmail(contactResult.getLong(CONTACT_FNMAILINDEX_COLUMN));
                c.setAccess(contactResult.getLong(CONTACT_ACCESS_COLUMN));
            }

            contactResult.close();
            return c;
        }

        else {
            contactResult.close();
            return null;
        }
    }

    /**
     * Gets a contact from an url (referring to the url field of Contact)
     * 
     * @param url
     * @return
     */
    public Contact getContactByURL(String url) {

        String whereContact = KEY_CONTACT_URL + "= \"" + url + "\"";
        Cursor contactResult = db.query(DATABASE_TABLE_CONTACT, null,
                whereContact, null, null, null, null);

        Contact c = null;
        if (contactResult.getCount() > 0) {

            c = new Contact();
            if (contactResult.moveToFirst()) {
                c.setId(contactResult.getLong(KEY_ID_COLUMN));
                c.setGroupId(contactResult.getInt(CONTACT_GROUP_COLUMN));
                c.setAlias(contactResult.getString(CONTACT_ALIAS_COLUMN));
                c.setUrl(contactResult.getString(CONTACT_URL_COLUMN));
                c.setUpdated(contactResult.getLong(CONTACT_UPDATE_COLUMN));
                c.setPhoneId(contactResult.getLong(CONTACT_PHONEID_COLUMN));

                ByteArrayInputStream bais = new ByteArrayInputStream(
                        contactResult.getBlob(CONTACT_PERSON_COLUMN));
                ObjectInputStream ois;
                try {
                    ois = new ObjectInputStream(bais);
                    c.setPerson((Person) ois.readObject());
                    ois.close();
                    bais.close();
                } catch (StreamCorruptedException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }

                c.setLetterId(contactResult.getLong(CONTACT_LETTERID_COLUMN));
                c.setPhoneIndex(contactResult
                        .getLong(CONTACT_PHONEINDEX_COLUMN));
                c.setMailIndex(contactResult.getLong(CONTACT_MAILINDEX_COLUMN));
                c.setFnletter(contactResult.getLong(CONTACT_FNLETTERID_COLUMN));
                c
                        .setFnphone(contactResult
                                .getLong(CONTACT_FNPHONEINDEX_COLUMN));
                c.setFnmail(contactResult.getLong(CONTACT_FNMAILINDEX_COLUMN));
                c.setAccess(contactResult.getLong(CONTACT_ACCESS_COLUMN));
            }
        }
        contactResult.close();
        return c;
    }

    /**
     * Gets a contact from an url (referring to the url field of Contact)
     * 
     * @param url
     * @return
     */
    public long getContactIdByURL(String url) {

        String whereContact = KEY_CONTACT_URL + "= \"" + url + "\"";
        Cursor contactResult = db.query(DATABASE_TABLE_CONTACT,
                new String[] { KEY_ID }, whereContact, null, null, null, null);

        if (contactResult.getCount() > 0) {

            if (contactResult.moveToFirst()) {
                long result = contactResult.getLong(KEY_ID_COLUMN);
                contactResult.close();
                return result;
            }
        }
        contactResult.close();
        return -1;
    }

    /**
     * Updates the contact with the person inside
     * 
     * @param contact
     * @return
     */
    public long updateContact(Contact contact) {

        String whereContact = KEY_ID + "=" + contact.getId();

        ByteArrayOutputStream baos = new ByteArrayOutputStream();

        try {
            ObjectOutputStream oos = new ObjectOutputStream(baos);
            oos.writeObject(contact.getPerson());
            oos.close();
            baos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        ContentValues contactValues = new ContentValues();
        contactValues.put(KEY_CONTACT_PERSON, baos.toByteArray());
        contactValues.put(KEY_CONTACT_GROUP, contact.getGroupId());
        contactValues.put(KEY_CONTACT_URL, contact.getUrl());
        contactValues.put(KEY_CONTACT_ALIAS, contact.getAlias());
        contactValues.put(KEY_CONTACT_UPDATE, contact.getUpdated());
        contactValues.put(KEY_CONTACT_PHONEID, contact.getPhoneId());
        contactValues.put(KEY_CONTACT_ACCESS, contact.getAccess());

        if (contact.getLetterId() != -1)
            decrementIndex(contact.getLetterId(), DATABASE_TABLE_LETTERINDEX);
        if (contact.getPhoneIndex() != -1)
            decrementIndex(contact.getPhoneIndex(), DATABASE_TABLE_PHONEINDEX);
        if (contact.getMailIndex() != -1)
            decrementIndex(contact.getMailIndex(), DATABASE_TABLE_MAILINDEX);
        if (contact.getLetterId() != -1)
            decrementIndex(contact.getFnletter(), DATABASE_TABLE_FNLETTERINDEX);
        if (contact.getPhoneIndex() != -1)
            decrementIndex(contact.getFnphone(), DATABASE_TABLE_FNPHONEINDEX);
        if (contact.getMailIndex() != -1)
            decrementIndex(contact.getFnmail(), DATABASE_TABLE_FNMAILINDEX);

        String name = contact.getPerson().getName();
        String firstName = contact.getPerson().getFirstname();
        long index;

        index = getIndex(name, DATABASE_TABLE_LETTERINDEX);
        incrementIndex(index, DATABASE_TABLE_LETTERINDEX);
        contactValues.put(KEY_CONTACT_LETTERID, index);

        if (contact.getPerson().getTels().size() > 0) {
            index = getIndex(name, DATABASE_TABLE_PHONEINDEX);
            incrementIndex(index, DATABASE_TABLE_PHONEINDEX);
            contactValues.put(KEY_CONTACT_PHONEINDEX, index);
        }

        if (contact.getPerson().getEmails().size() > 0) {
            index = getIndex(name, DATABASE_TABLE_MAILINDEX);
            incrementIndex(index, DATABASE_TABLE_MAILINDEX);
            contactValues.put(KEY_CONTACT_MAILINDEX, index);
        }

        if (firstName != null && firstName.length() > 0 && name.length() > 0
                && !(firstName.substring(0, 1).equals(name.substring(0, 1)))) {
            index = getIndex(firstName, DATABASE_TABLE_FNLETTERINDEX);
            incrementIndex(index, DATABASE_TABLE_FNLETTERINDEX);
            contactValues.put(KEY_CONTACT_FNLETTERID, index);

            if (contact.getPerson().getTels().size() > 0) {
                index = getIndex(firstName, DATABASE_TABLE_FNPHONEINDEX);
                incrementIndex(index, DATABASE_TABLE_FNPHONEINDEX);
                contactValues.put(KEY_CONTACT_FNPHONEINDEX, index);
            }

            if (contact.getPerson().getEmails().size() > 0) {
                index = getIndex(firstName, DATABASE_TABLE_FNMAILINDEX);
                incrementIndex(index, DATABASE_TABLE_FNMAILINDEX);
                contactValues.put(KEY_CONTACT_FNMAILINDEX, index);
            }
        }

        db.update(DATABASE_TABLE_CONTACT, contactValues, whereContact, null);
        return contact.getId();

    }

    /**
     * Updates a full contact and updates the update timestamp of it
     * 
     * @param contact
     * @return
     */
    public long updateFullContactWithTimestamp(Contact contact) {
        contact.setUpdated(new Date().getTime());
        return updateContact(contact);
    }

    /**
     * Updates only the contact and not the person inside the contact.
     * 
     * @param contact
     * @return
     */
    public long updateContactOnly(Contact contact) {

        String whereContact = KEY_ID + "=" + contact.getId();

        ContentValues contactValues = new ContentValues();
        contactValues.put(KEY_CONTACT_GROUP, contact.getGroupId());
        contactValues.put(KEY_CONTACT_URL, contact.getUrl());
        contactValues.put(KEY_CONTACT_ALIAS, contact.getAlias());
        contactValues.put(KEY_CONTACT_UPDATE, contact.getUpdated());
        contactValues.put(KEY_CONTACT_PHONEID, contact.getPhoneId());
        contactValues.put(KEY_CONTACT_ACCESS, contact.getAccess());
        db.update(DATABASE_TABLE_CONTACT, contactValues, whereContact, null);

        return contact.getId();

    }

    /**
     * Return if the contact already exist in the database
     * 
     * @param url
     * @return
     */
    public boolean hasContact(String url) {
        Contact contact = getContactByURL(url);
        return contact != null;
    }

    /**
     * Get all contact in the database and all Person in the contact
     * 
     * @return
     */
    public List<Contact> getAllContacts() {
        SQLiteCursor cursor = (SQLiteCursor) db.query(DATABASE_TABLE_CONTACT, null, null,
                null, null, null, null);
        List<Contact> list = (List<Contact>) new ArrayList<Contact>();
        cursor.moveToFirst();
        for (int i = 0; i < cursor.getCount(); i++, cursor.moveToNext()) {
            Contact contact = new Contact();
            contact.setId(cursor.getLong(KEY_ID_COLUMN));
            contact.setAlias(cursor.getString(CONTACT_ALIAS_COLUMN));
            contact.setGroupId(cursor.getInt(CONTACT_GROUP_COLUMN));
            contact.setUrl(cursor.getString(CONTACT_URL_COLUMN));

            ByteArrayInputStream bais = new ByteArrayInputStream(cursor
                    .getBlob(CONTACT_PERSON_COLUMN));
            ObjectInputStream ois;
            try {
                ois = new ObjectInputStream(bais);
                contact.setPerson((Person) ois.readObject());
                ois.close();
                bais.close();
            } catch (StreamCorruptedException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }

            contact.setUpdated(cursor.getLong(CONTACT_UPDATE_COLUMN));
            contact.setPhoneId(cursor.getLong(CONTACT_PHONEID_COLUMN));
            contact.setLetterId(cursor.getLong(CONTACT_LETTERID_COLUMN));
            contact.setPhoneIndex(cursor.getLong(CONTACT_PHONEINDEX_COLUMN));
            contact.setMailIndex(cursor.getLong(CONTACT_MAILINDEX_COLUMN));
            contact.setFnletter(cursor.getLong(CONTACT_FNLETTERID_COLUMN));
            contact.setFnphone(cursor.getLong(CONTACT_FNPHONEINDEX_COLUMN));
            contact.setFnmail(cursor.getLong(CONTACT_FNMAILINDEX_COLUMN));
            contact.setAccess(cursor.getLong(CONTACT_ACCESS_COLUMN));
            list.add(contact);
        }
        cursor.close();
        return sortContacts(list);
    }
    
    /**
     * Get all contact in the database and all Person in the contact
     * 
     * @return
     */
    public List<Contact> getAllContactsWithoutPerson() {
        SQLiteCursor cursor = (SQLiteCursor) db.query(DATABASE_TABLE_CONTACT, new String[] {
                KEY_ID, KEY_CONTACT_ALIAS, KEY_CONTACT_URL }, null,
                null, null, null, null);
        List<Contact> list = (List<Contact>) new ArrayList<Contact>();
        cursor.moveToFirst();
        for (int i = 0; i < cursor.getCount(); i++, cursor.moveToNext()) {
            Contact contact = new Contact();
            contact.setId(cursor.getLong(KEY_ID_COLUMN));
            contact.setAlias(cursor.getString(1));
            contact.setUrl(cursor.getString(2));
            list.add(contact);
        }
        cursor.close();
        return sortContacts(list);
    }

    /**
     * GetAllContacts from a table of IDs
     * 
     * @return
     */
    public List<Contact> getAllContactsFromTable(long[] table) {
        ArrayList<Contact> list = new ArrayList<Contact>();

        for (int i = 0; i < table.length; i++) {
            Contact c = getContact(table[i]);
            if (c != null)
                list.add(c);
        }

        return sortContacts(list);
    }

    public Contact addContact(Person person) {
        Contact c = new Contact();
        c.setPerson(person);
        c.setAlias(person.getFullName());
        long l = insertContact(c);
        c.setId(l);
        return c;
    }

    public Contact addContact(Contact contact) {
        long l = insertContact(contact);
        contact.setId(l);
        return contact;
    }

    public Contact addContact(Person person, String url) {

        long old = getContactIdByURL(url);
        if (old != -1) {
            Contact oldC = getContact(old);
            person.setId(oldC.getPerson().getId());
            oldC.setPerson(person);

            updateContact(oldC);

            return oldC;
        } else {
            Contact c = new Contact();
            c.setPerson(person);
            c.setUrl(url);
            c.setAlias(person.getFullName());

            long l = insertContact(c);
            c.setId(l);
            return c;
        }
    }

    public static List<Contact> sortContacts(List<Contact> list) {
        Collections.sort(list, new Comparator<Contact>() {
            @Override
            public int compare(Contact object1, Contact object2) {
                return (int) (object2.getAccess() - object1.getAccess());
            }
        });
        return list;
    }

    public long getIndex(String letter, String indexT) {

        if (letter == null)
            return -1;
        if (letter.length() > 1)
            letter = letter.substring(0, 1);

        String fullLetter = letter.toUpperCase();
        if (!alphabet.contains(fullLetter)) {
            fullLetter = "*";
        }

        String whereLetter = KEY_INDEX_LETTER + "= '" + fullLetter + "'";
        Cursor letterResult = db.query(indexT, new String[] { KEY_ID },
                whereLetter, null, null, null, null);

        if (letterResult.getCount() > 0) {
            letterResult.moveToFirst();
            long index = letterResult.getLong(KEY_ID_COLUMN);
            letterResult.close();
            return index;
        } else {
            letterResult.close();
            return insertIndex(fullLetter, indexT);
        }
    }

    public long insertIndex(String letter, String index) {

        ContentValues letterValues = new ContentValues();
        letterValues.put(KEY_INDEX_LETTER, letter);
        letterValues.put(KEY_INDEX_OCCU, 0);

        return db.insert(index, null, letterValues);
    }

    public long incrementIndex(long indexId, String index) {

        String whereLetter = KEY_ID + "=" + indexId;

        Cursor letterResult = db.query(index, new String[] { KEY_ID,
                KEY_INDEX_OCCU }, whereLetter, null, null, null, null);

        int occu;
        if (letterResult.getCount() > 0) {
            letterResult.moveToFirst();
            occu = letterResult.getInt(1);
            letterResult.close();
            ContentValues letterValues = new ContentValues();
            letterValues.put(KEY_INDEX_OCCU, occu + 1);
            db.update(index, letterValues, whereLetter, null);
            return occu + 1;
        } else {
            letterResult.close();
            return -1;
        }
    }

    public long decrementIndex(long letterIndexId, String index) {

        String whereLetter = KEY_ID + "=" + letterIndexId;

        Cursor letterResult = db.query(index, new String[] { KEY_ID,
                KEY_INDEX_OCCU }, whereLetter, null, null, null, null);

        int occu;
        if (letterResult.getCount() > 0) {
            letterResult.moveToFirst();
            occu = letterResult.getInt(1);
            letterResult.close();

            if (occu - 1 == 0) {
                removeIndex(letterIndexId, index);
                return 0;
            } else {
                ContentValues letterValues = new ContentValues();
                letterValues.put(KEY_INDEX_OCCU, occu - 1);
                db.update(index, letterValues, whereLetter, null);
                return occu - 1;
            }
        } else {
            letterResult.close();
            return -1;
        }
    }

    public long removeIndex(long indexId, String index) {

        String whereLetter = KEY_ID + "=" + indexId;
        db.delete(DATABASE_TABLE_LETTERINDEX, whereLetter, null);
        return 0;
    }

    public List<LetterIndex> getAllIndexes(String index) {
        List<LetterIndex> indexes = new ArrayList<LetterIndex>();
        Cursor allIndexes = db.query(index, null, null, null, null, null, null);

        if (allIndexes.moveToFirst()) {
            do {
                LetterIndex i = new LetterIndex(allIndexes
                        .getString(INDEX_LETTER_COLUMN), allIndexes
                        .getInt(INDEX_OCCU_COLUMN));

                i.setId(allIndexes.getLong(KEY_ID_COLUMN));

                if (index.equals(DATABASE_TABLE_LETTERINDEX)
                        || index.equals(DATABASE_TABLE_FNLETTERINDEX))
                    i.setType(LetterIndex.TYPE_ROOT);
                
                if (index.equals(DATABASE_TABLE_PHONEINDEX)
                        || index.equals(DATABASE_TABLE_FNPHONEINDEX))
                    i.setType(LetterIndex.TYPE_PHONE);
                
                if (index.equals(DATABASE_TABLE_MAILINDEX)
                        || index.equals(DATABASE_TABLE_FNMAILINDEX))
                    i.setType(LetterIndex.TYPE_MAIL);
                
                indexes.add(i);
            } while (allIndexes.moveToNext());
        }

        allIndexes.close();

        Collections.sort(indexes, new Comparator<LetterIndex>() {
            @Override
            public int compare(LetterIndex object1, LetterIndex object2) {
                return -object1.getLetter().toUpperCase().compareTo(
                        object2.getLetter().toUpperCase());
            }
        });
        return indexes;
    }

    /**
     * Gets all the contacts in a specific letterindex
     * 
     * @param indexId
     *            The Letter Index to get all the contacts for
     * @return An ArrayList of contacts
     */
    public List<Contact> getAllContactsFromIndex(long indexId, String index) {
        List<Contact> groupContacts = new ArrayList<Contact>();

        String whereContacts = index + "=" + indexId;
        Cursor validContacts = db.query(DATABASE_TABLE_CONTACT, null,
                whereContacts, null, null, null, null);

        if (validContacts.moveToFirst()) {

            do {
                Contact contact = new Contact();
                contact.setId(validContacts.getLong(KEY_ID_COLUMN));
                contact.setGroupId(validContacts.getInt(CONTACT_GROUP_COLUMN));

                ByteArrayInputStream bais = new ByteArrayInputStream(
                        validContacts.getBlob(CONTACT_PERSON_COLUMN));
                ObjectInputStream ois;
                try {
                    ois = new ObjectInputStream(bais);
                    contact.setPerson((Person) ois.readObject());
                    ois.close();
                    bais.close();
                } catch (StreamCorruptedException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }

                contact.setUrl(validContacts.getString(CONTACT_URL_COLUMN));
                contact.setAlias(validContacts.getString(CONTACT_ALIAS_COLUMN));
                contact
                        .setUpdated(validContacts
                                .getLong(CONTACT_UPDATE_COLUMN));
                contact.setPhoneId(validContacts
                        .getLong(CONTACT_PHONEID_COLUMN));
                contact.setLetterId(validContacts
                        .getLong(CONTACT_LETTERID_COLUMN));
                contact.setPhoneIndex(validContacts
                        .getLong(CONTACT_PHONEINDEX_COLUMN));
                contact.setMailIndex(validContacts
                        .getLong(CONTACT_MAILINDEX_COLUMN));
                contact.setFnletter(validContacts
                        .getLong(CONTACT_FNLETTERID_COLUMN));
                contact.setFnphone(validContacts
                        .getLong(CONTACT_FNPHONEINDEX_COLUMN));
                contact.setFnmail(validContacts
                        .getLong(CONTACT_FNMAILINDEX_COLUMN));
                contact.setAccess(validContacts.getLong(CONTACT_ACCESS_COLUMN));

                groupContacts.add(contact);
            } while (validContacts.moveToNext());

        }
        validContacts.close();

        return groupContacts;
    }

    /**
     * Get the total number of contacts
     * 
     * @return The number of contacts
     */
    public int getNumberOfContacts() {
        int result = 0;
        for (LetterIndex li : getAllIndexes(DATABASE_TABLE_LETTERINDEX)) {
            result = result + li.getOccu();
        }
        return result;
    }

    public static List<LetterIndex> indexFuuuuusion(List<LetterIndex> index1,
            List<LetterIndex> index2) {
        ArrayList<LetterIndex> result = new ArrayList<LetterIndex>();

        ArrayList<LetterIndex> temp1 = new ArrayList<LetterIndex>();
        ArrayList<LetterIndex> temp2 = new ArrayList<LetterIndex>();

        for (LetterIndex li : index2) {
            li.setId2(li.getId());
            li.setId(-1);
        }

        for (LetterIndex li1 : index1) {
            for (LetterIndex li2 : index2) {

                if (li1.getLetter().equals(li2.getLetter())) {
                    temp1.add(li1);
                    temp2.add(li2);
                    LetterIndex lif = new LetterIndex(li1.getLetter(), li1
                            .getOccu()
                            + li2.getOccu());
                    lif.setType(li1.getType());
                    lif.setId(li1.getId());
                    lif.setId2(li2.getId2());
                    result.add(lif);
                    break;
                }
            }
        }
        index1.removeAll(temp1);
        index2.removeAll(temp2);

        result.addAll(index1);
        result.addAll(index2);

        Collections.sort(result, new Comparator<LetterIndex>() {
            @Override
            public int compare(LetterIndex object1, LetterIndex object2) {
                return -object1.getLetter().toUpperCase().compareTo(
                        object2.getLetter().toUpperCase());
            }
        });

        return result;
    }
}
